package com.zr.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.zr.dao.E_userDao;
import com.zr.model.User;
import com.zr.model.UserMsg;
import com.zr.service.mainserver;
import com.zr.util.JDBCUtil;

/**
 * Z_UserDaoImpl 实现Z_UserDao接口 进行z_user表的数据处理
 * 
 * @author lzq
 *
 */
public class E_userDaoImpl implements E_userDao {

	@Override
	public User userLoginDao(String uname, String upsw) {
		User user = null;
		Connection con = JDBCUtil.getConnection();
		PreparedStatement pre = null;
		StringBuffer sql = new StringBuffer("");
		sql.append("select e_user.uid,e_user.uname,e_user.upsw,e_user.rid ");
		sql.append("from e_user ");
		sql.append("where e_user.uname=? and e_user.upsw=MD5(?)");
		try {
			pre = con.prepareStatement(sql.toString());
			pre.setString(1, uname);
			pre.setString(2, upsw);
			ResultSet res = pre.executeQuery();
			if (res.next()) {
				user = new User();
				user.setUid(res.getInt("uid"));
				user.setUname(res.getString("uname"));
				user.setUpsw(res.getString("upsw"));
				user.setRid(res.getInt("rid"));
				return user;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.closeJDBC(pre, con);
		}
		return user;
	}

	@Override
	public boolean addUser(String uname, String upsw,int rid) {
		Connection con = JDBCUtil.getConnection();
		PreparedStatement pre = null;
		StringBuffer sql = new StringBuffer("insert into e_user(uname,upsw,rid) values(?,MD5(?),?) ");
		try {
			pre = con.prepareStatement(sql.toString());
			pre.setString(1, uname);
			pre.setString(2, upsw);
			pre.setInt(3, rid);
			int count = pre.executeUpdate();
			if(count>0){
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.closeJDBC(pre, con);
		}
		return false;
	}

	@Override
	public boolean selectUserNameByUname(String uname) {
		Connection con = JDBCUtil.getConnection();
		PreparedStatement pre = null;
		StringBuffer sql = new StringBuffer("select e_user.uname from e_user ");
		sql.append("where e_user.uname=? ");
		try {
			pre = con.prepareStatement(sql.toString());
			pre.setString(1, uname);
			ResultSet res = pre.executeQuery();
			if(res.next()){
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.closeJDBC(pre, con);
		}
		return false;
	}

	@Override
	public List<UserMsg> selectAllUserMsgByPageByPagesize(int startPage, int pageSize) {
		List<UserMsg> userMsgs = new ArrayList<UserMsg>();
		Connection con = JDBCUtil.getConnection();
		PreparedStatement pre = null;
		StringBuffer sql = new StringBuffer("select e_user.uname,e_user.rid from e_user limit ?,?");
		try {
			pre = con.prepareStatement(sql.toString());
			pre.setInt(1, startPage);
			pre.setInt(2, pageSize);
			ResultSet res = pre.executeQuery();
			while(res.next()){
				UserMsg userMsg = new UserMsg();
				userMsg.setUname(res.getString("uname"));
				int rid = res.getInt("rid");
				if(rid == 1){
					userMsg.setRname("系统管理员");
				}else if(rid == 2){
					userMsg.setRname("知识管理员");
				}else {
					userMsg.setRname("普通用户");
				}
				userMsgs.add(userMsg);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.closeJDBC(pre, con);
		}
		return userMsgs;
	}

	@Override
	public int selectAllUserNumber() {
		Connection con = JDBCUtil.getConnection();
		PreparedStatement pre = null;
		StringBuffer sql = new StringBuffer("select count(*) as userNumber from e_user ");
		try {
			pre = con.prepareStatement(sql.toString());
			ResultSet res = pre.executeQuery();
			if(res.next()){
				return res.getInt("userNumber");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.closeJDBC(pre, con);
		}
		return 0;
	}

	@Override
	public boolean deleteUserMsgByUnames(String[] unames) {	
		Connection con = JDBCUtil.getConnection();
		PreparedStatement pre = null;
		StringBuffer sql = new StringBuffer("DELETE FROM e_user WHERE uname=?");
		try {
			con.setAutoCommit(false);
			pre = con.prepareStatement(sql.toString());
			for (int i = 0; i < unames.length; i++) {
				pre.setString(1, unames[i]);
				pre.addBatch();
			}
			pre.executeBatch();
			con.commit();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.closeJDBC(pre, con);
		}
		return false;
	}

	@Override
	public int[] selectCountUser() {
		int[] countUsers = new int[3];
		Connection con = JDBCUtil.getConnection();
		PreparedStatement pre = null;
		StringBuffer sql1 = new StringBuffer("select count(*) as userNumber from e_user where e_user.rid=1 ");
		StringBuffer sql2 = new StringBuffer("select count(*) as userNumber from e_user where e_user.rid=2 ");
		StringBuffer sql3 = new StringBuffer("select count(*) as userNumber from e_user where e_user.rid=3 ");
		try {
			for (int i = 0; i < countUsers.length; i++) {
				if(i==0){
					pre = con.prepareStatement(sql1.toString());
				}else if(i==1){
					pre = con.prepareStatement(sql2.toString());
				}else{
					pre = con.prepareStatement(sql3.toString());
				}
				ResultSet res = pre.executeQuery();
				if(res.next()){
					countUsers[i] = res.getInt("userNumber");
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.closeJDBC(pre, con);
		}
		return countUsers;
	}
}
